import { getDeviceParameterDb } from '../lib/device-parameter-database';
import * as fs from 'fs';
import * as path from 'path';

interface DataRedundancyReport {
  hardcodedMappings: {
    files: string[];
    totalMappings: number;
    canBeReplaced: boolean;
  };
  hardcodedSpecialtyData: {
    files: string[];
    dbEquivalentExists: boolean;
    redundantData: Array<{
      source: string;
      deviceCount: number;
      duplicatesInDb: number;
    }>;
  };
  unusedJsonFiles: {
    files: Array<{
      path: string;
      size: number;
      isEmpty: boolean;
      lastModified: string;
    }>;
  };
  orphanedTables: {
    tables: string[];
    recommendAction: string;
  };
  duplicateDataSources: {
    issues: Array<{
      type: string;
      description: string;
      files: string[];
      recommendation: string;
    }>;
  };
}

async function analyzeDataRedundancy(): Promise<DataRedundancyReport> {
  console.log('🔍 开始分析项目中的硬编码数据和冗余数据问题...\n');

  const report: DataRedundancyReport = {
    hardcodedMappings: {
      files: [],
      totalMappings: 0,
      canBeReplaced: false
    },
    hardcodedSpecialtyData: {
      files: [],
      dbEquivalentExists: false,
      redundantData: []
    },
    unusedJsonFiles: {
      files: []
    },
    orphanedTables: {
      tables: [],
      recommendAction: ''
    },
    duplicateDataSources: {
      issues: []
    }
  };

  const db = getDeviceParameterDb();

  // 1. 分析硬编码映射文件
  console.log('📋 1. 分析硬编码表名映射...');
  const hardcodedMappingFiles = [
    'scripts/fix-device-mappings-to-real-tables.ts',
    'scripts/fix-device-mappings-from-original.ts'
  ];

  report.hardcodedMappings.files = hardcodedMappingFiles.filter(file => 
    fs.existsSync(path.join(process.cwd(), file))
  );

  // 统计硬编码映射数量
  if (fs.existsSync('scripts/fix-device-mappings-to-real-tables.ts')) {
    const content = fs.readFileSync('scripts/fix-device-mappings-to-real-tables.ts', 'utf8');
    const mappingMatches = content.match(/'表\s*[CEHILcehil]\.[\d-]+'/g);
    report.hardcodedMappings.totalMappings = mappingMatches ? mappingMatches.length / 2 : 0; // 除以2因为每个映射有两个变体
  }

  report.hardcodedMappings.canBeReplaced = true; // 基于之前的验证结果
  console.log(`  找到 ${report.hardcodedMappings.files.length} 个硬编码映射文件`);
  console.log(`  硬编码映射总数: ${report.hardcodedMappings.totalMappings}`);
  console.log(`  可被动态映射替代: ${report.hardcodedMappings.canBeReplaced ? '✅ 是' : '❌ 否'}`);

  // 2. 分析硬编码专业数据
  console.log('\n📋 2. 分析硬编码专业数据...');
  const specialtyDataFile = 'lib/other-specialty-data.ts';
  if (fs.existsSync(specialtyDataFile)) {
    report.hardcodedSpecialtyData.files.push(specialtyDataFile);
    
    // 检查数据库中是否存在等效数据
    const dbSpecialties = (db as any).db.prepare(`
      SELECT specialty, COUNT(*) as count 
      FROM device_type_mappings 
      WHERE specialty != 'piping' 
      GROUP BY specialty
    `).all();

    report.hardcodedSpecialtyData.dbEquivalentExists = dbSpecialties.length > 0;
    
    // 分析冗余数据
    const hardcodedSpecialties = ['electrical', 'hvac', 'communication', 'instrumentation'];
    hardcodedSpecialties.forEach(specialty => {
      const dbData = dbSpecialties.find((s: any) => s.specialty === `${specialty}专业`);
      report.hardcodedSpecialtyData.redundantData.push({
        source: `${specialty}Data in other-specialty-data.ts`,
        deviceCount: 5, // 根据文件内容估算
        duplicatesInDb: dbData ? dbData.count : 0
      });
    });
  }

  console.log(`  硬编码专业数据文件: ${report.hardcodedSpecialtyData.files.length}`);
  console.log(`  数据库中存在等效数据: ${report.hardcodedSpecialtyData.dbEquivalentExists ? '✅ 是' : '❌ 否'}`);

  // 3. 检查未使用的JSON文件
  console.log('\n📋 3. 检查未使用的JSON数据文件...');
  const dataDir = path.join(process.cwd(), 'data');
  if (fs.existsSync(dataDir)) {
    const jsonFiles = fs.readdirSync(dataDir).filter(file => file.endsWith('.json'));
    
    for (const file of jsonFiles) {
      const filePath = path.join(dataDir, file);
      const stats = fs.statSync(filePath);
      const content = fs.readFileSync(filePath, 'utf8');
      const isEmpty = content.trim() === '[]' || content.trim() === '{}';
      
      report.unusedJsonFiles.files.push({
        path: file,
        size: stats.size,
        isEmpty,
        lastModified: stats.mtime.toISOString()
      });
    }
  }

  console.log(`  JSON文件总数: ${report.unusedJsonFiles.files.length}`);
  const emptyFiles = report.unusedJsonFiles.files.filter(f => f.isEmpty);
  console.log(`  空文件数量: ${emptyFiles.length}`);

  // 4. 检查孤立的参数表
  console.log('\n📋 4. 检查孤立的参数表...');
  const allTables = (db as any).db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'table_%'
  `).all();

  const referencedTables = new Set();
  const mappings = (db as any).db.prepare(`
    SELECT naming_table, position_table, base_params_table, tech_spec_table 
    FROM device_type_mappings
  `).all();

  mappings.forEach((m: any) => {
    if (m.naming_table) referencedTables.add(m.naming_table);
    if (m.position_table) referencedTables.add(m.position_table);
    if (m.base_params_table) referencedTables.add(m.base_params_table);
    if (m.tech_spec_table) referencedTables.add(m.tech_spec_table);
  });

  report.orphanedTables.tables = allTables
    .map((t: any) => t.name)
    .filter((name: string) => !referencedTables.has(name));

  report.orphanedTables.recommendAction = report.orphanedTables.tables.length > 0 
    ? '建议检查这些表是否为历史遗留数据，考虑清理'
    : '无孤立表，数据库结构良好';

  console.log(`  参数表总数: ${allTables.length}`);
  console.log(`  被引用的表: ${referencedTables.size}`);
  console.log(`  孤立表数量: ${report.orphanedTables.tables.length}`);

  // 5. 检查重复数据源
  console.log('\n📋 5. 检查重复数据源...');
  
  // Excel文件 vs 数据库
  const excelDir = path.join(process.cwd(), 'excel');
  let excelFileCount = 0;
  if (fs.existsSync(excelDir)) {
    const excelFiles = fs.readdirSync(excelDir, { recursive: true })
      .filter(file => file.toString().endsWith('.xlsx'));
    excelFileCount = excelFiles.length;
  }

  if (excelFileCount > 0) {
    report.duplicateDataSources.issues.push({
      type: 'Excel文件与数据库重复',
      description: `发现 ${excelFileCount} 个Excel文件，可能与数据库中的数据重复`,
      files: ['excel/'],
      recommendation: '确认数据已导入数据库后，考虑归档Excel文件'
    });
  }

  // 硬编码数据 vs 数据库
  if (report.hardcodedSpecialtyData.dbEquivalentExists) {
    report.duplicateDataSources.issues.push({
      type: '硬编码专业数据冗余',
      description: '专业数据同时存在于代码文件和数据库中',
      files: ['lib/other-specialty-data.ts'],
      recommendation: '使用数据库数据，移除硬编码文件'
    });
  }

  // JSON文件冗余
  if (emptyFiles.length > 0) {
    report.duplicateDataSources.issues.push({
      type: '空JSON文件',
      description: `发现 ${emptyFiles.length} 个空的JSON数据文件`,
      files: emptyFiles.map(f => f.path),
      recommendation: '删除空的JSON文件，减少项目冗余'
    });
  }

  console.log(`  发现 ${report.duplicateDataSources.issues.length} 个数据源重复问题`);

  return report;
}

function generateCleanupRecommendations(report: DataRedundancyReport): void {
  console.log('\n💡 数据清理建议:');
  console.log('==================');

  // 1. 硬编码映射替换
  if (report.hardcodedMappings.canBeReplaced) {
    console.log('\n1. 🔄 替换硬编码表名映射:');
    console.log('   ✅ 已验证动态映射可以完全替代硬编码映射');
    console.log('   📝 行动方案:');
    report.hardcodedMappings.files.forEach(file => {
      console.log(`      - 修改 ${file}，使用 mapDisplayNameToTableName() 替代硬编码对象`);
    });
    console.log('   💰 收益: 减少代码维护成本，新增表时自动支持');
  }

  // 2. 硬编码专业数据处理
  if (report.hardcodedSpecialtyData.dbEquivalentExists) {
    console.log('\n2. 🗃️ 处理硬编码专业数据:');
    console.log('   ✅ 数据库已包含完整的专业分类数据');
    console.log('   📝 行动方案:');
    console.log('      - 删除 lib/other-specialty-data.ts 文件');
    console.log('      - 修改相关导入，改为从数据库查询数据');
    console.log('      - 更新专业选择组件，使用数据库API');
    console.log('   💰 收益: 数据一致性，减少代码冗余');
  }

  // 3. JSON文件清理
  if (report.unusedJsonFiles.files.length > 0) {
    console.log('\n3. 🧹 清理JSON数据文件:');
    const emptyFiles = report.unusedJsonFiles.files.filter(f => f.isEmpty);
    const nonEmptyFiles = report.unusedJsonFiles.files.filter(f => !f.isEmpty);
    
    if (emptyFiles.length > 0) {
      console.log(`   ❌ 删除空文件 (${emptyFiles.length}个):`);
      emptyFiles.forEach(file => {
        console.log(`      - data/${file.path}`);
      });
    }
    
    if (nonEmptyFiles.length > 0) {
      console.log(`   🔍 检查非空文件 (${nonEmptyFiles.length}个):`);
      nonEmptyFiles.forEach(file => {
        console.log(`      - data/${file.path} (${file.size} bytes, 修改于 ${new Date(file.lastModified).toLocaleDateString()})`);
      });
      console.log('      建议: 确认这些文件是否仍在使用，如否则删除');
    }
  }

  // 4. 孤立表处理
  if (report.orphanedTables.tables.length > 0) {
    console.log('\n4. 🏚️ 处理孤立的参数表:');
    console.log(`   ⚠️ 发现 ${report.orphanedTables.tables.length} 个未被引用的参数表`);
    console.log('   📝 行动方案:');
    console.log('      - 检查这些表是否为历史遗留数据');
    console.log('      - 确认无用后可考虑删除');
    console.log('   ⚠️ 孤立表列表:');
    report.orphanedTables.tables.slice(0, 10).forEach(table => {
      console.log(`      - ${table}`);
    });
    if (report.orphanedTables.tables.length > 10) {
      console.log(`      ... 还有 ${report.orphanedTables.tables.length - 10} 个`);
    }
  }

  // 5. 总体建议
  console.log('\n5. 🎯 优先级建议:');
  console.log('   🥇 高优先级: 替换硬编码映射（影响维护性）');
  console.log('   🥈 中优先级: 清理空JSON文件（减少项目体积）');
  console.log('   🥉 低优先级: 删除硬编码专业数据（数据已在数据库中）');
  console.log('   ⚠️ 谨慎处理: 孤立表删除（需要仔细验证）');
}

async function main() {
  try {
    const report = await analyzeDataRedundancy();
    generateCleanupRecommendations(report);
    
    console.log('\n📊 分析完成! 请根据建议进行数据清理和代码重构。');
    
  } catch (error) {
    console.error('❌ 分析失败:', error);
    process.exit(1);
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  main();
}